﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Models;
using System.Data.SqlClient;

namespace DataManager
{
    public class DBAccess
    {
        private static DBAccess instance;

        public static DBAccess getInstance()
        {
            if (instance == null)
            {
                instance = new DBAccess();
            }
            return instance;
        }

        public List<Film> getListFilms()
        {
            List<Film> listeFilms = new List<Film>();
            try
            {   

                SqlCommand reqSQL = new SqlCommand("SELECT * FROM Film");

                reqSQL.Connection = DBConnection.getInstance().getConnection();

                SqlDataReader dr = reqSQL.ExecuteReader();

                while (dr.Read())
                {
                    Film nouveauFilm = new Film();
                    nouveauFilm.Id = dr.GetInt32(0);
                    nouveauFilm.Titre = dr.GetString(1);
                    nouveauFilm.DatePublication = dr.GetDateTime(2);
                    nouveauFilm.Prix = dr.GetInt32(3);
                    nouveauFilm.Genre = dr.GetString(4);
                    listeFilms.Add(nouveauFilm);
                }

            }
            catch (Exception)
            {

            }
            finally
            {
                DBConnection.getInstance().getConnection().Close();
            }

            return listeFilms;
        }

        public Film getFilm(int id)
        {
            Film monFilm = null;
            try
            {

                SqlCommand reqSQL = new SqlCommand("SELECT * FROM FILM WHERE idFilm = @id");
                reqSQL.Parameters.Add(new SqlParameter("@id", id));

                reqSQL.Connection = DBConnection.getInstance().getConnection();

                SqlDataReader dr = reqSQL.ExecuteReader();



                if (dr.Read())
                {
                    monFilm = new Film();
                    monFilm.Id = id;
                    monFilm.Titre = dr.GetString(1);
                    monFilm.DatePublication = dr.GetDateTime(2);
                    monFilm.Prix = dr.GetInt32(3);
                    monFilm.Genre = dr.GetString(4);
                }

            }
            catch (Exception)
            {

            }
            finally
            {
                DBConnection.getInstance().getConnection().Close();
            }
            return monFilm;

        }

        public void createFilm(Film nouveauFilm)
        {
            try
            {
                SqlCommand reqSQL = new SqlCommand("INSERT INTO Film VALUES(@titre,@date,@prix,@genre)");

                reqSQL.Parameters.Add(new SqlParameter("@titre", nouveauFilm.Titre));
                reqSQL.Parameters.Add(new SqlParameter("@date", nouveauFilm.DatePublication));
                reqSQL.Parameters.Add(new SqlParameter("@prix", nouveauFilm.Prix));
                reqSQL.Parameters.Add(new SqlParameter("@genre", nouveauFilm.Genre));

                reqSQL.Connection = DBConnection.getInstance().getConnection();

                reqSQL.ExecuteNonQuery();
            }
            catch (Exception)
            {
            }
            finally
            {
                DBConnection.getInstance().getConnection().Close();
            }


        }

        public void updateFilm(Film updatedMovie)
        {
            try
            {
                SqlCommand reqSQL = new SqlCommand("UPDATE Film SET titre = @titre, datePublication = @date, prix = @prix, genre = @genre WHERE idFilm = @id");

                reqSQL.Parameters.Add(new SqlParameter("@id", updatedMovie.Id));
                reqSQL.Parameters.Add(new SqlParameter("@titre", updatedMovie.Titre));
                reqSQL.Parameters.Add(new SqlParameter("@date", updatedMovie.DatePublication));
                reqSQL.Parameters.Add(new SqlParameter("@prix", updatedMovie.Prix));
                reqSQL.Parameters.Add(new SqlParameter("@genre", updatedMovie.Genre));

                reqSQL.Connection = DBConnection.getInstance().getConnection();

                reqSQL.ExecuteNonQuery();
            }
            catch (Exception)
            {

            }
            finally
            {
                DBConnection.getInstance().getConnection().Close();
            }

        }

        public void deleteFilm(int idFilm)
        {
            try
            {
                SqlCommand reqSQL = new SqlCommand("DELETE FROM Film where idFilm = @id");

                reqSQL.Parameters.Add(new SqlParameter("@id", idFilm));

                reqSQL.Connection = DBConnection.getInstance().getConnection();

                reqSQL.ExecuteNonQuery();
            }
            catch (Exception)
            {

            }
            finally
            {
                DBConnection.getInstance().getConnection().Close();
            }

        }
    }    
        
}
